This is something we expect to be simple, but it has a lot of secrets. Using parameters in DAX queries is essential, as any other query: The parameter transfers the filtering to the server side instead of the report side and optimize the report performance.

However, DAX queries have many secrets to use parameters. If you don’t handle these secrets, you will get the famous report builder error: The query contains the parameter which is not declared

Report Builder: The parameters’ structure

The parameters structure for SQL queries or DAX queries is very similar, with one difference: In SQL queries, when we use the parameters in the query, the entire structure is automatically built for us.

Using DAX parameters, in contrast, the entire structure needs to be manually configured.

This is the structure of a parameter in a query, using a SQL query as an example:

1) Using the Query Designer, we create a filter and mark the filter as parameter

2) This action creates a parameter on the report level

3) In the dataset properties, the parameter in the report level is linked with a parameter in the query level

Here is an interesting trick in SQL queries: This part is automatic. Leaving it automatic we are led to believe we are using the report parameter in the SQL query.

However, they are not the same. The report parameters and the SQL parameters are different and linked to each other in the dataset properties. They could have different names, if we would like so, but we don’t even notice this part.

4) Here is where the link between the report parameter and the query parameter happens

5) We use the query parameter in the query

The order can vary. We could create a parameter on the report level first, link at dataset properties and use in the query. However, the structure, the link between the elements, remains the same.

Parameter in DAX: The Secret

The structure remains the same, but we have some important differences which make it a challenge.

The link is not automatic. You need to establish the link manually.

This first secret means you need to create the parameter on the report level first. After that, in the properties of the dataset, you need to create the query parameter and link with the dataset parameter.

The next secret is the one which challenged me and took so long to discover:

DAX parameters don’t use ‘@’ symbol in their two configuration windows

The DAX parameters only use the ‘@’ symbol in the actual query. In the two configuration windows where they need to be included the parameter doesn’t use the ‘@’ symbol. If you don’t follow this rule, you will get the famous error message: The query contains the parameter which is not declared

The third and final secret:

DAX has an additional parameter configuration window, inside query builder

The parameter needs to be manually configured in this window. If you forget this, you get the error. In the same way as the dataset properties, the parameter doesn’t include ‘@’ as well.

In this window, the parameter needs to be linked to the dimension and hierarchy it’s intended to filter, including a default value. It’s a requirement, but I couldn’t identify any way this affects the query at all.

In summary: follow these rules, ensure the DAX is correct and the parameterized query will be successful.

Conclusion

There may be different sequences of steps to reach the same result. However, the rules are the same. Follow the rules, and the query will work. I hope to bring some light to a long-lasting challenge